package formin.mediaLibrary.io;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.swing.DefaultListModel;

import formin.mediaLibrary.model.SettingsData;

	
public class DataBaseManager {
	public static final String DATABASE_TABLE_SHOWS = "ShowsTable";
	public static final String DATABASE_CATAGORY_NAME = "Name";
	
	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

	private SettingsData sqlSettings = null;
	
	//constructor - generates a references to the core and gets the sql server settings
	public DataBaseManager(SettingsData settings){
		sqlSettings = settings;
	}
	
	//connects to mySQLServer Database
	public boolean connectToDatabase(){

		try{
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://"+sqlSettings.getSQLServerAddress(), sqlSettings.getSQLServerUserName(), sqlSettings.getSQLServerPassword());
			statement = connect.createStatement();
		}catch (Exception e){
			System.out.println("DBM: Error Connecting To mySQLServer");
			System.out.println(e.getMessage());
			return false;
		}	
		try {
			statement.execute("use "+sqlSettings.getSQLServerDataBase());
		} catch (SQLException e) {
			System.out.println("DBM: Error Using Database:"+sqlSettings.getSQLServerDataBase());
			System.out.println(e.getMessage());
			
			//todo - decision popup
			createDatabases();
			return false;
		}
		return true;
	}
	private void reConnectToDatabase(){

	}
	
	
	public String[] getShowsList() {
		DefaultListModel results = new DefaultListModel();
		try {
			resultSet = statement.executeQuery("select * from "+ DATABASE_TABLE_SHOWS);
			while(resultSet.next()){
				results.addElement(resultSet.getString("catagory"));
				
			}				
		} catch (SQLException e) {
			
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			return (String[])results.toArray();
		}
		return (String[])results.toArray();
	}
	
	

	public void readListDatabase(){
		try {
			resultSet = statement.executeQuery("select * from "+ "constants.DATABASE_TABLE_LIST");
			while(resultSet.next()){
				//coreRef.addListGUI(resultSet.getString(constants.DATABASE_FEILD_ITEM));
			}				
		} catch (SQLException e) {
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
		return;
	}
	
	
	
	// write to database functions
	public void addCatagoryDBM(String catagory){
		try{
			statement.execute("INSERT INTO " + "constants.DATABASE_TABLE_CATAGORIES" + "(" +
					"constants.DATABASE_FEILD_CATAGORY" + ") values('"+catagory+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
			createListTable();
		}
	}
	
	public void addListItemDBM(String item){
		try{
			statement.execute("INSERT INTO " + "constants.DATABASE_TABLE_LIST" + "(" + "constants.DATABASE_FEILD_ITEM" + ") values('"+item+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
	}
		
	public void saveRecipeDBM(String name, int rating, int difficulty, String time, String catagory, String process ){
		try{
			statement.execute("INSERT INTO recipelist (name, rating, difficulty, time, catagory, process) " +
					"values('"+name+"',"+rating+","+difficulty+",'"+time+"','"+catagory+"','"+process+"')");
		}catch(SQLException e){
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			System.out.println(e.getCause());
		}
	}

	// creation functions for all the tables and databases on the mySQLServer
	private boolean createDatabases(){
		System.out.println("DBM: Creating New Databases :");
		try{
			statement.execute("create database "+sqlSettings.getSQLServerDataBase());
		}catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		//retry connecting after creating database
		if (sqlSettings != null){
			return connectToDatabase();
		}
		return false;
	}
	
	private void createRecipeListTable(){
		System.out.println("DBM: Creating New Recipe Table :");
		try{
			statement.execute("create table recipelist( id int primary key NOT NULL auto_increment," +
					"name varchar(30), rating int, difficulty int, " +
					"time varchar(8),catagory varchar(30), " +
					"process text)");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
	
	private void createListTable(){
		System.out.println("DBM: Creating New List Table :");
		try{
			statement.execute("create table list (id int primary key not NULL auto_increment, item varchar(30))");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
	
	private void createIngredientsTable(int id){
		System.out.println("DBM: Creating New Ingredients Table :"+id);
		try{
			statement.execute("create table ingredients"+id+"( id int primary key NOT NULL auto_increment, quantity varchar(8), units varchar(8), ingredient varchar(30))");
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}
	}
}